﻿namespace SRM.DataObjects
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.ComponentModel;
    using System.Data;
    using System.Data.SqlClient;

    /// <summary>
    /// Summary description for SRM_Applicant
    /// </summary>
    [DataObjectAttribute(true)]
    public class SRM_Applicant
    {
        public SRM_Applicant()
        {
        }

        public static void AddApplicant(string email, string password)
        {
            //cmd.CommandType = CommandType.StoredProcedure
            //cmd.CommandText = "SRM_CREATEAPPLICANT_SP"
            //da.SelectCommand = cmd
            //cmd.Parameters.Add("Name", SqlDbType.VarChar).Value = strName
            //cmd.Parameters.Add("UserEmail", SqlDbType.VarChar).Value = strUserEmail
            //cmd.Parameters.Add("Password", SqlDbType.VarChar).Value = strPassword
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                cmd.CommandText = "INSERT INTO SRM_Applicant_TBL (EmailAddress, Password, CreatedDate, FormComplete) VALUES (@Email, @Password, GETDATE(), 99)";
                cmd.Parameters.AddWithValue("@Email", email.Trim());
                cmd.Parameters.AddWithValue("@Password", Help.ComputeMD5(password.Trim()));
                conn.Open();
                cmd.ExecuteNonQuery();
            }
        }

        public static void AddResumeFile(int applicant_key, byte[] content, string fileType, string fileName)
        {
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                string existing = "N";

                cmd.CommandText = "GetApplicant;";
                DataTable dtApplicant = GetApplicant(applicant_key);
                DataRow dr = dtApplicant.Rows[0];

                if (dr["ResumeFileName"] != DBNull.Value)
                    existing = "Y";

                conn.Open();
                using (System.Data.SqlClient.SqlTransaction sqlTran = conn.BeginTransaction())
                {
                    cmd.Transaction = sqlTran;

                    cmd.CommandText = "INSERT SRM_Resume_Tbl (Content, Existing, FileType, FileName) VALUES (@Content, @Existing, @FileType, @FileName);SELECT SCOPE_IDENTITY();";
                    cmd.Parameters.AddWithValue("@Content", content);
                    cmd.Parameters.AddWithValue("@Existing", existing);
                    cmd.Parameters.AddWithValue("@FileType", fileType.Trim());
                    cmd.Parameters.AddWithValue("@FileName", fileName.Trim());
                    object result = cmd.ExecuteScalar();

                    cmd.CommandText = "UPDATE SRM_Applicant_Tbl SET ResumeFileName = @ResumeFileName, ResumeID = @ResumeId WHERE ApplicantKey = @ApplicantKey";
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@ApplicantKey", applicant_key);
                    cmd.Parameters.AddWithValue("@ResumeFileName", fileName.Trim());
                    cmd.Parameters.AddWithValue("@ResumeId", result);
                    cmd.ExecuteNonQuery();

                    sqlTran.Commit();
                }
            }
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetApplicant(int applicant_key)
        {
            DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                cmd.CommandText = "SELECT ApplicantKey, ResumeFileName, PDPAConsent FROM SRM_Applicant_TBL WHERE ApplicantKey = @applicant_key;";
                cmd.Parameters.AddWithValue("@applicant_key", applicant_key);
                da.Fill(ds);

                dtResult = ds.Tables[0];
            }

            return dtResult;
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetApplicantByEmail(string email)
        {
            DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                cmd.CommandText = "SELECT ApplicantKey, ResumeFileName FROM SRM_Applicant_TBL WHERE EmailAddress = @email;";
                cmd.Parameters.AddWithValue("@email", email);
                da.Fill(ds);

                dtResult = ds.Tables[0];
            }

            return dtResult;
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetPersonalParticulars(string applicantKey)
        {
            DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);


                cmd.CommandText = "SELECT * FROM SRM_Applicant_TBL WHERE ApplicantKey = @applicant_key;";
                cmd.Parameters.AddWithValue("@applicant_key", applicantKey);
                da.Fill(ds);

                dtResult = ds.Tables[0];
            }

            return dtResult;
        }

        #region Helper methods
        
        public static DataTable GetSRM_Code(string codeType, string useSAPCode)
        {
            DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);


                cmd.CommandText = string.Format("SELECT * FROM SRM_Code WHERE CodeType = '{0}' AND IsSAPCode = {1};", codeType, useSAPCode);
                da.Fill(ds);

                dtResult = ds.Tables[0];
            }

            return dtResult;
        }

        #endregion Helper methods

        public static bool FamilyChildrenCheck(string applicantKey)
        {
            DataTable dtResult = null;
            bool checkResult = false;

            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                cmd.CommandText = string.Format(@"
SELECT  CASE WHEN 
        (SELECT  NoOfChildren FROM    SRM_Applicant_TBL WHERE ApplicantKey = {0}) = COUNT(*) THEN
        1
        ELSE
        0
        END AS 'Match'
FROM    SRM_ApplicantFamilyMembers_Tbl
WHERE   ApplicantKey = {0} AND Relationship IN (024, 081)", applicantKey);
                da.Fill(ds);

                dtResult = ds.Tables[0];

                if (dtResult.Rows.Count > 0)
                {
                    if (dtResult.Rows[0]["Match"].ToString() == "1")
                        checkResult = true;
                }
            }

            return checkResult;
        }

        public static bool HasEmergencyContact(string applicantKey)
        {
            DataTable dtResult = null;
            bool checkResult = false;

            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                cmd.CommandText = string.Format(@"
SELECT  * 
FROM    SRM_ApplicantFamilyMembers_Tbl
WHERE   EmergencyContact = 'YES' AND ApplicantKey = {0}", applicantKey);
                da.Fill(ds);

                dtResult = ds.Tables[0];

                if (dtResult.Rows.Count > 0)
                {
                    checkResult = true;
                }
            }

            return checkResult;
        }


        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetFamilyMembers(string applicantKey)
        {
            DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                cmd.CommandText = string.Format("SELECT * FROM SRM_ApplicantFamilyMembers_Tbl WHERE ApplicantKey = {0};", applicantKey);
                da.Fill(ds);

                dtResult = ds.Tables[0];
            }

            return dtResult;

        }

        public static void UpdatePDPAConsent(string applicantKey,
            bool pdpaCheck)
        {
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = @"UPDATE  SRM_Applicant_TBL 
SET     PDPAConsent = @PDPAConsent
WHERE   ApplicantKey = @ApplicantKey;";

                cmd.Parameters.AddWithValue("@ApplicantKey", applicantKey);
                if (pdpaCheck)
                    cmd.Parameters.AddWithValue("@PDPAConsent", Help.GetValueOrDBNull(DateTime.Now.ToString("yyyy-MM-dd")));
                else
                    cmd.Parameters.AddWithValue("@PDPAConsent", DBNull.Value);

                conn.Open();
                cmd.ExecuteNonQuery();
            }
        }


        [DataObjectMethodAttribute(DataObjectMethodType.Update, true)]
        public static void UpdateAdditionPersonalParticulars(string applicantKey,
            DateTime? dateOfBirth, string race, string maritalStatus, DateTime? marriedSince, string gender, string noOfChildren, string dialect, string religion)
        {
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = @"UPDATE  SRM_Applicant_TBL 
SET     DateOfBirth     = @DateOfBirth,
        Race            = @Race,
        MaritalStatus   = @MaritalStatus,
        MarriedSince    = @MarriedSince,
        Gender          = @Gender,
        NoOfChildren    = @NoOfChildren,
        Dialect         = @Dialect,
        Religion        = @Religion
WHERE   ApplicantKey = @ApplicantKey;";

                cmd.Parameters.AddWithValue("@ApplicantKey", applicantKey);
                cmd.Parameters.AddWithValue("@DateOfBirth", Help.GetValueOrDBNull(dateOfBirth));
                cmd.Parameters.AddWithValue("@Race", Help.GetValueOrDBNull(race));
                cmd.Parameters.AddWithValue("@MaritalStatus", Help.GetValueOrDBNull(maritalStatus));
                cmd.Parameters.AddWithValue("@MarriedSince", Help.GetValueOrDBNull(marriedSince));
                cmd.Parameters.AddWithValue("@Gender", Help.GetValueOrDBNull(gender));
                cmd.Parameters.AddWithValue("@NoOfChildren", Help.GetValueOrDBNull(noOfChildren));
                cmd.Parameters.AddWithValue("@Dialect", Help.GetValueOrDBNull(dialect));
                cmd.Parameters.AddWithValue("@Religion", Help.GetValueOrDBNull(religion));

                conn.Open();
                cmd.ExecuteNonQuery();
            }
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetAdditionPersonalParticulars(string applicantKey)
        {
            DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                cmd.CommandText = string.Format(@"SELECT  DateOfBirth, 
        Race, 
        MaritalStatus, 
        MarriedSince, 
        Gender, 
        NoOfChildren, 
        Dialect, 
        Religion 
FROM    SRM_Applicant_TBL 
WHERE   ApplicantKey = {0};", applicantKey);
                da.Fill(ds);

                dtResult = ds.Tables[0];
            }

            return dtResult;
        } // public static DataTable GetAdditionPersonalParticulars(string applicantKey)

        [DataObjectMethodAttribute(DataObjectMethodType.Update, true)]
        public static void UpdateNationalService(string applicantKey,
            string nsstatus, 
            string nsservicetypecode,
            string nsvocation,
            string nsservicerankcode,
            DateTime? nsenlistmentdate, 
            string ns, 
            DateTime? nsorddate,            
            string rsservicetypecode, 
            string rsvocation, 
            string rsservicerankcode, 
            DateTime? rscompletiondate
            )
        {
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = @"UPDATE  SRM_Applicant_TBL
SET     NSStatus            = @NSStatus,
        NSServiceTypeCode   = @NSServiceTypeCode, 
        NSVocation          = @NSVocation,
        NSServiceRankCode   = @NSServiceRankCode,
        NSEnlistmentDate    = @NSEnlistmentDate,
        NS                  = @NS,
        NSORDDate           = @NSORDDate,
        RSServiceTypeCode   = @RSServiceTypeCode,
        RSVocation          = @RSVocation,
        RSServiceRankCode   = @RSServiceRankCode,
        RSCompletionDate    = @RSCompletionDate
WHERE   ApplicantKey = @ApplicantKey;";

                cmd.Parameters.AddWithValue("@ApplicantKey", applicantKey);

                cmd.Parameters.AddWithValue("@NS", Help.GetValueOrDBNull(ns));
                cmd.Parameters.AddWithValue("@NSServiceTypeCode", Help.GetValueOrDBNull(nsservicetypecode));
                cmd.Parameters.AddWithValue("@NSStatus", Help.GetValueOrDBNull(nsstatus));
                cmd.Parameters.AddWithValue("@NSVocation", Help.GetValueOrDBNull(nsvocation));
                cmd.Parameters.AddWithValue("@NSServiceRankCode", Help.GetValueOrDBNull(nsservicerankcode));
                cmd.Parameters.AddWithValue("@NSORDDate", Help.GetValueOrDBNull(nsorddate));

                cmd.Parameters.AddWithValue("@NSEnlistmentDate", Help.GetValueOrDBNull(nsenlistmentdate));
                cmd.Parameters.AddWithValue("@RSServiceTypeCode", Help.GetValueOrDBNull(rsservicetypecode));
                cmd.Parameters.AddWithValue("@RSVocation", Help.GetValueOrDBNull(rsvocation));
                cmd.Parameters.AddWithValue("@RSServiceRankCode", Help.GetValueOrDBNull(rsservicerankcode));
                cmd.Parameters.AddWithValue("@RSCompletionDate", Help.GetValueOrDBNull(rscompletiondate));
                
                conn.Open();
                cmd.ExecuteNonQuery();
            }
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetNationalService(string applicantKey)
        {
            DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                cmd.CommandText = string.Format(@"SELECT  NS, 
        NSServiceTypeCode, 
        NSStatus,
        NSVocation,
        NSServiceRankCode,
        NSORDDate,
        NSRank,
        NSEnlistmentDate,
        RSServiceTypeCode,
        RSVocation,
        RSServiceRankCode,
        RSCompletionDate
FROM    SRM_Applicant_TBL 
WHERE   ApplicantKey = {0};", applicantKey);

                da.Fill(ds);

                dtResult = ds.Tables[0];
            }

            return dtResult;
        } // public static DataTable GetAdditionPersonalParticulars(string applicantKey)

        [DataObjectMethodAttribute(DataObjectMethodType.Update, true)]
        public static void UpdateFurtherInformation(string applicantKey,
            string furtherinfo1, string furtherinfo2, string furtherinfo3, 
            string furtherinfo4, string furtherinfo5, string furtherinfo6, 
            string furtherinfo7, string furtherinfo8, string furtherinfo9,
            string furtherinfo2details, string furtherinfo3details, string furtherinfo4details, 
            string furtherinfo5details, string furtherinfo6details, string furtherinfo7details,
            string furtherinfo8details, string furtherinfo9details)
        {
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = @"UPDATE  SRM_Applicant_TBL
SET     FurtherInfo1 = @FurtherInfo1,
        FurtherInfo2 = @FurtherInfo2,
        FurtherInfo3 = @FurtherInfo3,
        FurtherInfo4 = @FurtherInfo4,
        FurtherInfo5 = @FurtherInfo5,
        FurtherInfo6 = @FurtherInfo6,
        FurtherInfo7 = @FurtherInfo7,
        FurtherInfo8 = @FurtherInfo8,
        FurtherInfo9 = @FurtherInfo9,
        FurtherInfo2Details = @FurtherInfo2Details,
        FurtherInfo3Details = @FurtherInfo3Details,
        FurtherInfo4Details = @FurtherInfo4Details,
        FurtherInfo5Details = @FurtherInfo5Details,
        FurtherInfo6Details = @FurtherInfo6Details,
        FurtherInfo7Details = @FurtherInfo7Details,
        FurtherInfo8Details = @FurtherInfo8Details,
        FurtherInfo9Details = @FurtherInfo9Details
WHERE   ApplicantKey = @ApplicantKey;";

                cmd.Parameters.AddWithValue("@ApplicantKey", applicantKey);

                cmd.Parameters.AddWithValue("@FurtherInfo1", Help.GetValueOrDBNull(furtherinfo1));
                cmd.Parameters.AddWithValue("@FurtherInfo2", Help.GetValueOrDBNull(furtherinfo2));
                cmd.Parameters.AddWithValue("@FurtherInfo3", Help.GetValueOrDBNull(furtherinfo3));
                cmd.Parameters.AddWithValue("@FurtherInfo4", Help.GetValueOrDBNull(furtherinfo4));
                cmd.Parameters.AddWithValue("@FurtherInfo5", Help.GetValueOrDBNull(furtherinfo5));
                cmd.Parameters.AddWithValue("@FurtherInfo6", Help.GetValueOrDBNull(furtherinfo6));
                cmd.Parameters.AddWithValue("@FurtherInfo7", Help.GetValueOrDBNull(furtherinfo7));
                cmd.Parameters.AddWithValue("@FurtherInfo8", Help.GetValueOrDBNull(furtherinfo8));
                cmd.Parameters.AddWithValue("@FurtherInfo9", Help.GetValueOrDBNull(furtherinfo9));

                cmd.Parameters.AddWithValue("@FurtherInfo2Details", Help.GetValueOrDBNull(furtherinfo2details));
                cmd.Parameters.AddWithValue("@FurtherInfo3Details", Help.GetValueOrDBNull(furtherinfo3details));
                cmd.Parameters.AddWithValue("@FurtherInfo4Details", Help.GetValueOrDBNull(furtherinfo4details));
                cmd.Parameters.AddWithValue("@FurtherInfo5Details", Help.GetValueOrDBNull(furtherinfo5details));
                cmd.Parameters.AddWithValue("@FurtherInfo6Details", Help.GetValueOrDBNull(furtherinfo6details));
                cmd.Parameters.AddWithValue("@FurtherInfo7Details", Help.GetValueOrDBNull(furtherinfo7details));
                cmd.Parameters.AddWithValue("@FurtherInfo8Details", Help.GetValueOrDBNull(furtherinfo8details));
                cmd.Parameters.AddWithValue("@FurtherInfo9Details", Help.GetValueOrDBNull(furtherinfo9details));
                

                conn.Open();
                cmd.ExecuteNonQuery();
            }
        }


        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetFurtherInformation(string applicantKey)
        {
            DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                cmd.CommandText = string.Format(@"SELECT  FurtherInfo1,
        FurtherInfo2,
        FurtherInfo3,
        FurtherInfo4,
        FurtherInfo5,
        FurtherInfo6,
        FurtherInfo7,
        FurtherInfo8,
        FurtherInfo9,
        FurtherInfo2Details,
        FurtherInfo3Details,
        FurtherInfo4Details,
        FurtherInfo5Details,
        FurtherInfo6Details,
        FurtherInfo7Details,
        FurtherInfo8Details,
        FurtherInfo9Details,
        ConsentDeclaration
FROM    SRM_Applicant_TBL
WHERE   ApplicantKey = {0};", applicantKey);

                da.Fill(ds);

                dtResult = ds.Tables[0];
            }

            return dtResult;
        } // public static DataTable GetFurtherInformation(string applicantKey)

        public static DataTable GetApplicantAccount(string emailAddress)
        {
            DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);


                cmd.CommandText = "SELECT ApplicantKey, EmailAddress, Password, Stage FROM SRM_Applicant_Tbl WHERE EmailAddress = @EmailAddress;";
                cmd.Parameters.AddWithValue("@EmailAddress", emailAddress);
                da.Fill(ds);

                dtResult = ds.Tables[0];
            }

            return dtResult;
        }

        public static void SetApplicantAccountPassword(string emailAddress, string passwordMD5)
        {
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = "UPDATE SRM_Applicant_Tbl SET Password = @NewPassword WHERE   EmailAddress = @EmailAddress;";
                cmd.Parameters.AddWithValue("@EmailAddress", emailAddress);
                cmd.Parameters.AddWithValue("@NewPassword", passwordMD5);

                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }

        //this.Salutation_RadioButtonList.SelectedValue = this.row.Field<string>("Salutation");
        //this.GivenName_TextBox.Text = this.row.Field<string>("GivenName");
        //this.Surname_TextBox.Text = this.row.Field<string>("LastName");
        //this.NameFormat_DropDownList.SelectedValue = this.row.Field<string>("NameFormat");
        //this.DisplayNameResult_Literal.Text = "TODO";
        //this.ChineseCharacter_TextBox.Text = this.row.Field<string>("ChineseName");

        public static void UpdatePersonalParticulars(string applicantKey,
            string vacancymedium, string agencyname,
            string salutation, string givename, string lastname, string nameformat, string chinesename,
            string nricno, string nrictype, DateTime? nricissuingdate, string passportno,
            string citizenship, string placeofbirth, string drivingclass, 
            string blockno, string levelno, string unitno, string streetname, string buildingname, string postalcode,
            string countrycode, string city, string state, string mobileno, string homeno, 
            string yrsexperience, string periodnotice, DateTime? availability, string askingsalary
            )
        {
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = @"UPDATE  SRM_Applicant_TBL 
SET     VacancyMedium   = @VacancyMedium,
        AgencyName      = @AgencyName,
        Salutation      = @Salutation,
        GivenName       = @GivenName,
        LastName        = @LastName,
        NameFormat      = @NameFormat,
        ChineseName     = @ChineseName,
        NRICNo          = @NRICNo,
        NRICType        = @NRICType,
        NRICIssuingDate = @NRICIssuingDate,
        PassportNo      = @PassportNo,
        Citizenship     = @Citizenship,
        PlaceOfBirth    = @PlaceOfBirth,
        DrivingClass    = @DrivingClass,
        BlockNo         = @BlockNo,
        LevelNo         = @LevelNo,
        UnitNo          = @UnitNo,
        StreetName      = @StreetName,
        BuildingName    = @BuildingName,
        PostalCode      = @PostalCode,
        CountryCode     = @CountryCode,
        City            = @City,
        State           = @State,
        MobileNo        = @MobileNo,
        HomeNo          = @HomeNo,
        YrsExperience   = @YrsExperience,
        PeriodNotice    = @PeriodNotice,
        Availability    = @Availability,
        AskingSalary    = @AskingSalary,
        
        FormVersion     = 1
WHERE   ApplicantKey = @ApplicantKey;";

                cmd.Parameters.AddWithValue("@ApplicantKey", applicantKey);
                cmd.Parameters.AddWithValue("@VacancyMedium", Help.GetValueOrDBNull(vacancymedium));
                cmd.Parameters.AddWithValue("@AgencyName", Help.GetValueOrDBNull(agencyname));

                cmd.Parameters.AddWithValue("@Salutation", Help.GetValueOrDBNull(salutation));
                cmd.Parameters.AddWithValue("@GivenName", Help.GetValueOrDBNull(givename));
                cmd.Parameters.AddWithValue("@LastName", Help.GetValueOrDBNull(lastname));
                cmd.Parameters.AddWithValue("@NameFormat", Help.GetValueOrDBNull(nameformat));
                cmd.Parameters.AddWithValue("@ChineseName", Help.GetValueOrDBNull(chinesename));

                cmd.Parameters.AddWithValue("@NRICNo", Help.GetValueOrDBNull(nricno));
                cmd.Parameters.AddWithValue("@NRICType", Help.GetValueOrDBNull(nrictype));
                cmd.Parameters.AddWithValue("@NRICIssuingDate", Help.GetValueOrDBNull(nricissuingdate));
                cmd.Parameters.AddWithValue("@PassportNo", Help.GetValueOrDBNull(passportno));


                cmd.Parameters.AddWithValue("@Citizenship", Help.GetValueOrDBNull(citizenship));
                cmd.Parameters.AddWithValue("@PlaceOfBirth", Help.GetValueOrDBNull(placeofbirth));
                cmd.Parameters.AddWithValue("@DrivingClass", Help.GetValueOrDBNull(drivingclass));

                cmd.Parameters.AddWithValue("@BlockNo", Help.GetValueOrDBNull(blockno));
                cmd.Parameters.AddWithValue("@LevelNo", Help.GetValueOrDBNull(levelno));
                cmd.Parameters.AddWithValue("@UnitNo", Help.GetValueOrDBNull(unitno));
                cmd.Parameters.AddWithValue("@StreetName", Help.GetValueOrDBNull(streetname));
                cmd.Parameters.AddWithValue("@BuildingName", Help.GetValueOrDBNull(buildingname));
                cmd.Parameters.AddWithValue("@PostalCode", Help.GetValueOrDBNull(postalcode));

                cmd.Parameters.AddWithValue("@CountryCode", Help.GetValueOrDBNull(countrycode));
                cmd.Parameters.AddWithValue("@City", Help.GetValueOrDBNull(city));
                cmd.Parameters.AddWithValue("@State", Help.GetValueOrDBNull(state));
                cmd.Parameters.AddWithValue("@MobileNo", Help.GetValueOrDBNull(mobileno));
                cmd.Parameters.AddWithValue("@HomeNo", Help.GetValueOrDBNull(homeno));

                cmd.Parameters.AddWithValue("@YrsExperience", Help.GetValueOrDBNullForNumber(yrsexperience));
                cmd.Parameters.AddWithValue("@PeriodNotice", Help.GetValueOrDBNullForNumber(periodnotice));
                cmd.Parameters.AddWithValue("@Availability", Help.GetValueOrDBNull(availability));
                cmd.Parameters.AddWithValue("@AskingSalary", Help.GetValueOrDBNullForNumber(askingsalary));

                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw;
                }
                catch (Exception)
                {
                    throw;
                }
                
            }
        }

        public static void UpdateFreshGraduateStatus(string applicantKey, string freshGraduate)
        {
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = @"UPDATE  SRM_Applicant_TBL 
SET     FreshGraduate   = @FreshGraduate 
WHERE   ApplicantKey    = @ApplicantKey;";

                cmd.Parameters.AddWithValue("@ApplicantKey", applicantKey);
                cmd.Parameters.AddWithValue("@FreshGraduate", Help.GetValueOrDBNull(freshGraduate));
                
                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw;
                }
                catch (Exception)
                {
                    throw;
                }

            } // using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
        }


        public static DataTable GetPrevAndNextApplicant(string applicantKey)
        {
            DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                cmd.CommandText = string.Format(@"SELECT '1' AS 'Seq', MAX(ApplicantKey) AS ApplicantKey FROM SRM_Applicant_TBL WHERE ApplicantKey < {0}
UNION SELECT '2' AS 'Seq', MIN(ApplicantKey) AS ApplicantKey FROM SRM_Applicant_TBL WHERE ApplicantKey > {0};", applicantKey);

                da.Fill(ds);

                dtResult = ds.Tables[0];
            }

            return dtResult;
        }

        public static void SetFormComplete(string applicantKey)
        {
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = @"UPDATE  SRM_Applicant_TBL 
SET     FormComplete   = @FormComplete 
WHERE   ApplicantKey = @ApplicantKey;";

                cmd.Parameters.AddWithValue("@ApplicantKey", applicantKey);
                cmd.Parameters.AddWithValue("@FormComplete", 0);

                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw;
                }
                catch (Exception)
                {
                    throw;
                }

            }
        }

    }
}